DROP database if exists os;

CREATE DATABASE os;
DROP TABLE orders ;
USE os;

CREATE TABLE orders(

  id char(6) NOT NULL,
  product_id char(6) NOT NULL ,
  number int,
  price double ,
  create_time datetime,
  send_time datetime,
  confirm_time datetime,
  consignee varchar(20),
  consignee_phone varchar (11),
  consignee_address varchar (100),
  status varchar (100),
  primary key (id)
)character set utf8;

CREATE TABLE products(
  id char(6) not null,
  title varchar (20),
  price double ,
  stock int ,
  status varchar (10),
  primary key (id)
)character set utf8;

-- 关联外键
ALTER TABLE orders ADD CONSTRAINT FK_Reference_1 foreign key (product_id)
references products(id) on delete restrict on update restrict ;

show create table orders;
desc orders;


ALTER TABLE products DEFAULT CHARACTER SET utf8;

-- 修改表的字段的编码格式，不然无法插入中文
ALTER TABLE products CHANGE  title title VARCHAR(20) CHARACTER SET utf8 NOT  NULL ;
ALTER TABLE products CHANGE  status status VARCHAR(20) CHARACTER SET utf8 NOT  NULL ;

ALTER TABLE orders CHANGE  consignee consignee VARCHAR(20) CHARACTER SET utf8 NOT  NULL ;
ALTER TABLE orders CHANGE  consignee_phone consignee_phone VARCHAR(20) CHARACTER SET utf8 NOT  NULL ;
ALTER TABLE orders CHANGE  consignee_address consignee_address VARCHAR(20) CHARACTER SET utf8 NOT  NULL ;
ALTER TABLE orders CHANGE  status status VARCHAR(20) CHARACTER SET utf8 NOT  NULL ;


-- 修改表中的全部字段为utf8 但是收到了外键的影响，无法修改
ALTER TABLE products convert to character set utf8;


insert into products(id, title, price, stock, status) values
('100001','华为mate10',5399,100,'正常'),
('100002','华为P20',3999,100,'正常'),
('100003','华为2s ',1999,100,'正常'),
('100004','荣耀10 ',6999,100,'正常');


insert  into values('100001','100001',2,2499,now(),null ,null, 'lisi','15623151205','lisi','daifahuo');

SELECT * from products;
SELECT * from orders;



-- 事务隔离级别测试,在不同的会话窗口执行，注意时序图
-- 设置事务隔离级别 读未提交，这状况下，脏读，不可重复读，幻读都存在
set session  transaction isolation level read uncommitted ;

-- 脏读
-- 事务A
begin;
select stock from products  where id = '100001';
rollback ;
-- 事务B
begin ;
update products set stock = 0 where id = '100001'
rollback ;

-- 不可重复读
-- 事务A
begin ;
select stock from products where id = '100001';
select stock from products where id = '100001';
rollback ;
-- 事务B
begin ;
update products set stock = 100 where id = '100001';
commit ;

-- 幻读
-- 事务A
begin ;
update products set stock = 0 ;
select * from products;
rollback ;
-- 事务B
begin ;
insert into products values ('100005','',1999,100,'正常');
commit ;

